using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Custody : DataAccess
    {
        public Custody(string db_str)
        {
            CreateConnection(db_str);
        }
        public DataSet GetCustodyBalance(string account_no, string symbol, 
            DateTime history_date, string board_id)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT * FROM CustodyAccount WHERE client_id=@account_no";
                //cmd.Parameters.Add("@account_no", SqlDbType.NVarChar, 64).Value = account_no;
                cmd.CommandText = "sp_sel_CustodyAccount";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = int.MaxValue;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@trxn_date", SqlDbType.DateTime).Value = history_date;
                cmd.Parameters.Add("@client_id", SqlDbType.NVarChar, 10).Value = account_no;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 20).Value = board_id;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public DataSet GetStockBalSummary(DateTime history_date, string board_id)
        {
            DataSet cash_balance = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "SELECT * FROM CustodyAccount WHERE client_id=@account_no";
                //cmd.Parameters.Add("@account_no", SqlDbType.NVarChar, 64).Value = account_no;
                cmd.CommandText = "sp_sel_StockBalSummary";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = int.MaxValue;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@log_date", SqlDbType.DateTime).Value = history_date;               
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 20).Value = board_id;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(cash_balance);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cash_balance;
        }

        public DataSet GetCustodyTrans(DateTime from_date, DateTime to_date, string tran_type,
           string status, string client_id, string symbol)
        {
            DataSet custody_trans = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_CustodyTrans";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@FROM_DATE", SqlDbType.DateTime).Value = from_date;
                cmd.Parameters.Add("@TO_DATE", SqlDbType.DateTime).Value = to_date;
                cmd.Parameters.Add("@CLIENT_ID", SqlDbType.VarChar, 20).Value = client_id;
                cmd.Parameters.Add("@TRAN_TYPE", SqlDbType.VarChar, 4).Value = tran_type;
                cmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 1).Value = status;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(custody_trans);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return custody_trans;
        }

        public DataSet GetCustodySettlement(DateTime tran_date, string client_id)
        {
            DataSet custody_trans = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_EFCustodySettlement";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@tran_date", SqlDbType.DateTime).Value = tran_date;
                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 20).Value = client_id; 
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(custody_trans);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return custody_trans;
        }

        public void CreateAccount(string account_no, string symbol, decimal withdraw_qtty,
            decimal deposit_qtty, decimal blockade_qtty, decimal mortgage_qtty, string status)
        {            
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_ins_CustodyAccount";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@account_no", SqlDbType.VarChar, 20).Value = account_no;
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@withdraw_qtty", SqlDbType.Decimal).Value = withdraw_qtty;
                cmd.Parameters.Add("@deposit_qtty", SqlDbType.Decimal).Value = deposit_qtty;
                cmd.Parameters.Add("@blockade_qtty", SqlDbType.Decimal).Value = blockade_qtty;
                cmd.Parameters.Add("@mortgage_qtty", SqlDbType.Decimal).Value = mortgage_qtty;
                cmd.Parameters.Add("@status", SqlDbType.VarChar, 1).Value = status;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
    }
}
